package EDT.api;

import EDT.obj.Administrateur;
import EDT.obj.Intervenant;
import EDT.obj.Utilisateur;
import EDT.obj.Creneau;
import EDT.obj.Formation;
import EDT.obj.Ec;
import EDT.obj.Salle;
import EDT.obj.Ue;
import EDT.obj.Cferie;
import EDT.obj.FicheVoeux;
import EDT.obj.FormationConsult;
import EDT.obj.IntervUsers;
import EDT.obj.PlaquetteForm;
import EDT.obj.PlaquetteInterv;
import EDT.obj.Session;
import EDT.obj.Status;
import EDT.obj.Voeux;
import java.sql.Connection;
import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import java.util.HashMap;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;


class BDUser {
	
	private static HashMap<Integer,Integer> getPromotion(int idf){
            
        
                HashMap<Integer,Integer> res = new HashMap<>();
        try {
            Connection c = getConnection();
            Statement st = c.createStatement();
            ResultSet r =st.executeQuery("select idp,annee from promotion where idf="+idf+"order by annee DESC");
            
            while(r.next())
		{
				res.put(r.getInt(1), r.getInt(2));
		}
		
		if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
            
            
        } catch (SQLException | InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
	
            return res;
        }
        
        
        public static void main(String[] args){
            /*for (Formation f : getFormation()){*/
            /*
                HashMap<Integer, Date> hashMap = getGuidePeriode(21);
                for (Integer mapKey : hashMap.keySet()){
                   System.out.println(hashMap.get(mapKey).toString());
                }*/
            //}*/
            /*
            HashMap<Integer, String> hashMap = getJoursFrom("11-09-2013",10);
                for (Integer mapKey : hashMap.keySet()){
                   System.out.println("<option value='"+mapKey+"'>"+hashMap.get(mapKey)+"</option>");
                }
              */   
            
           //for (String i : getJoursFrom("11-09-2013",10))
           System.out.println(isKeyValid("862380d3"));
            
               
            
            
            //Calendar c = Calendar.getInstance();
            
            //DateFormat fullDateFormat = DateFormat.getDateTimeInstance(DateFormat.FULL,DateFormat.FULL);
           //System.out.println(getJourferie(2013));
            //System.out.println(fullDateFormat.format(c.getTime()));
                
                //System.out.println(AjouterFicheVoeuxParDefault(2013)) ;
            
            //System.out.println(API.generateKey()+" "+API.generateKey()+" "+API.generateKey());
         }
        
        
        public static ArrayList<Formation> getFormation() {
            ArrayList<Formation> ret = new ArrayList<>();
            
            
            try {
                DateFormat dateFormat = new SimpleDateFormat("yyyy");
                Date annee1 = new Date();
        
                Calendar cal = Calendar.getInstance(); 
                cal.setTime(annee1);
                int a = Integer.parseInt(dateFormat.format(cal.getTime()));
                
                
            Connection c = getConnection();
            Statement st = c.createStatement();
            
            
            ResultSet r =st.executeQuery("select f.idf,f.grade,f.niveau,"
                    +" f.filiere,f.parcours,f.typee,c.anneeEx "
                    +" from formation f,contrat c "
                    +" where c.idc = f.idcf  and c.anneeex >= "+a
                    +" Order by f.grade,f.niveau,f.filiere,f.parcours,f.typee,c.anneeEx");          
            
            
            while(r.next())
            {
                ret.add(new Formation(r.getInt(1),r.getString(2)+" Niveau "+r.getString(3)+" "+r.getString(4)+" "+r.getString(5)+" "+r.getString(6),0,r.getInt(7)));	
            }
		
            
           
            
		if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}

        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }

           for (Formation f: ret){
      		f.setPeriodes(getNbrPeriode(f.getId()));
		
                f.setLesPromos(getPromotion(f.getId()));
                HashMap<Integer,Integer> grps = new HashMap<>();
                for (Integer mapKey : f.getLesPromos().keySet()){
                    grps.put(mapKey, getNbrGp(mapKey));
                }
                f.setNbrGpPromo(grps);
            }
             
            //Formation f= new Formation("L3 MAGE", 2);
            //f.getLesPromos().put(1, 2012);
            //ret.add(f);
            return ret;
        }

        
        public static int getNbrPeriode(int idf){
            int ret=0;
            
            Connection c;
        try {
            c = getConnection();
            Statement st = c.createStatement();
           
            int i=0;
            ResultSet r =st.executeQuery("select count (idsem) from semestre where idf="+idf);
            while(r.next())
            {
                        
			ret = r.getInt(1);
                   
            }    
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            
            return ret;
        }
        
        
        public static HashMap<Integer,Date> getGuidePeriode(int idp ){
            HashMap<Integer,Date> ret = new HashMap<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            /* 
            ResultSet r =st.executeQuery("select distinct u.idec, s.nperiode from ec_ue u, semestre s, promotion p, ue ue "
                    +"where p.idp="+idp+" and p.idf = s.idf and s.idsem = ue.idsem and ue.idue = u.idue order by s.nperiode" );          
            */
            
             ResultSet r =st.executeQuery("select s.nperiode,pr.fin "
                     + " from periode pr,semestre s "
                    +" where pr.idp="+idp+" and pr.idsem = s.idsem "
                     + " order by s.nperiode" );          
            
             
            while(r.next())
            {
                    ret.put(r.getInt(1),r.getDate(2) );
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
           
            
            return ret;
        }
        
        
        public static ArrayList<Creneau> getlesCreneau(int idp, String periode,int nGp){
            
            ArrayList<Creneau> ret = new ArrayList<>();
            //System.out.println("idp= "+idp);
             Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             ResultSet r;
            String req ;
             
             if(periode.equals("all")){
                 
             req ="select ag.idp , ec.code , ec.nom , s.nom , i.prenom , i.nom , "
              + " j.datee , ag.debut , ag.fin , ag.typee , ag.visible, ag.idec , "
              + " ag.numg ,ag.idcr ,f.grade , f.niveau , f.typee, i.mail "
+" from agenda ag , ec ec , salle s , intervenant i , jour j, promotion p, formation f "
+" where ag.idp=p.idp and p.idf = f.idf and ag.idec =  ec.idec and ag.idp ="+idp
+ " and ag.ids = s.ids and ag.idi =i.idi and ag.idj = j.idj ";
  if (nGp != 0) {
                     req += " and (ag.numg = "+nGp+" or ag.typee = 'cm') ";
                 }            
 req +=" ORDER BY j.datee ASC, ag.debut ASC";          
             }else{
            
            req ="select distinct ag.idp , ec.code , ec.nom , s.nom , i.prenom , "
            + " i.nom , j.datee , ag.debut , ag.fin , ag.typee , ag.visible, "
            + " ag.idec, ag.numg, idcr ,f.grade , f.niveau , f.typee, i.mail "+
" from agenda ag , ec ec , salle s , intervenant i , jour j , ec_ue u, ue ue, "+
" semestre sm, promotion p, formation f, periode pr "+
" Where Ag.Idp ="+idp+"  And ag.Idp = p.Idp And P.Idf = F.Idf And Ag.Idec =  Ec.Idec "+
" And Ag.Ids = S.Ids And Ag.Idi =I.Idi And Ag.Idj = J.Idj "+
" And Sm.Nperiode = "+periode+" And Sm.Idf = P.Idf And Pr.Idp = Ag.Idp "+ 
" And Sm.Idsem =Pr.Idsem And J.Datee Between Pr.Debut And Pr.Fin ";
              if (nGp != 0) {
                     req += " and ( ag.numg = "+nGp+" or ag.typee = 'cm') ";
                 }            
 req +=" ORDER BY j.datee ASC, ag.debut ASC";
             }
            r = st.executeQuery(req);
            while(r.next())
            {
                if(r.getString(11).equals("true")){
                    ret.add(new Creneau(r.getInt(1),r.getString(3) , r.getString(4), r.getString(5)+" "+r.getString(6), r.getDate(7), r.getInt(8),r.getInt(9),r.getString(10),r.getInt(12),r.getInt(13),r.getInt(14),r.getString(15).substring(0, 1).toUpperCase()+String.valueOf(r.getInt(16))+r.getString(17).substring(0,1).toUpperCase(),r.getString(11),r.getString(18)));
                }
                
                    //ret.add(new Creneau(String.valueOf(r.getInt(1)),r.getString(14).substring(0, 1).concat(String.valueOf(r.getInt(15))+r.getString(16).subSequence(0, 1)) +" : "+r.getString(3) , r.getString(4), r.getString(5)+" "+r.getString(6), r.getDate(7), r.getInt(8),r.getInt(9),r.getString(10),r.getInt(12),r.getInt(13)));
                          
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
           
           
            
            return ret;
        }
        
        
        public static ArrayList<Creneau> getTouslesCreneau(int idp, String periode, int nGp){
             
        
            
            ArrayList<Creneau> ret = new ArrayList<>();
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             ResultSet r;
            String req;
             
             
                 
             if(periode.equals("all")){
                 
             req ="select ag.idp , ec.code , ec.nom , s.nom , i.prenom , i.nom , "
             + " j.datee , ag.debut , ag.fin , ag.typee , ag.visible, ag.idec , "
             + " ag.numg ,ag.idcr ,f.grade , f.niveau , f.typee, i.mail "+
"from agenda ag , ec ec , salle s , intervenant i , jour j, promotion p, formation f "+
"where ag.idp=p.idp and p.idf = f.idf and ag.idec =  ec.idec and ag.idp ="+idp+" and ag.ids = s.ids and ag.idi =i.idi and ag.idj = j.idj ";
                    if (nGp != 0) {
                        req += " and (ag.numg = "+nGp+" or ag.typee = 'cm') ";
                    }            
 req +=" ORDER BY j.datee ASC, ag.debut ASC";          
             }else{
              req ="select distinct ag.idp , ec.code , ec.nom , s.nom , i.prenom , i.nom , j.datee , ag.debut , ag.fin , ag.typee , ag.visible, ag.idec, ag.numg, idcr ,f.grade , f.niveau , f.typee, i.mail "+
"from agenda ag , ec ec , salle s , intervenant i , jour j , ec_ue u, ue ue,semestre sm, promotion p, formation f, periode pr "+
"Where Ag.Idp ="+idp+"  And ag.Idp = p.Idp And P.Idf = F.Idf And Ag.Idec =  Ec.Idec "+
"And Ag.Ids = S.Ids And Ag.Idi =I.Idi And Ag.Idj = J.Idj "+
"And Sm.Nperiode = "+periode+" And Sm.Idf = P.Idf And Pr.Idp = Ag.Idp "+ 
"And Sm.Idsem =Pr.Idsem And J.Datee Between Pr.Debut And Pr.Fin ";
              if (nGp != 0) {
                     req += " and ( ag.numg = "+nGp+" or ag.typee = 'cm') ";
                 }            
 req +=" ORDER BY j.datee ASC, ag.debut ASC";
             }
            r = st.executeQuery(req);
            while(r.next())
            {
   
                  ret.add(new Creneau(r.getInt(1),r.getString(3) , r.getString(4), r.getString(5)+" "+r.getString(6), r.getDate(7), r.getInt(8),r.getInt(9),r.getString(10),r.getInt(12),r.getInt(13),r.getInt(14),r.getString(15).substring(0, 1).toUpperCase()+String.valueOf(r.getInt(16))+r.getString(17).substring(0,1).toUpperCase(),r.getString(11),r.getString(18)));                         
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
           
           
            
            return ret;
        }
        
        
        public static ArrayList<Intervenant> getAllIntervenant(){
            
            ArrayList<Intervenant> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select  i.nom, i.prenom, i.idi from intervenant i where i.enservice='true' order by i.prenom" );          
            
            
            while(r.next())
            {
              ret.add(new Intervenant(r.getString(1),r.getString(2),r.getInt(3)));
            
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
        } 
        
          public static ArrayList<Intervenant> getIntervenantEtat(){
              ArrayList<Intervenant> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select  i.nom,i.prenom,i.idi,i.enservice  from intervenant i order by i.prenom");          
            
            
            while(r.next())
            {
                if(r.getString(4).equals("true")) {
                    ret.add(new Intervenant(r.getString(1), r.getString(2), r.getInt(3),"Activé"));
                }
                else {
                    ret.add(new Intervenant(r.getString(1), r.getString(2), r.getInt(3),"Désactivé"));
                }
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
          }
        
        public static ArrayList<Intervenant> getIntervenant(){
            
            ArrayList<Intervenant> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select  i.nom,i.prenom,i.idi, min(p.annee), max(p.annee) from intervenant i, agenda ag, promotion p "
                    +" where i.idi = ag.idi and ag.idp = p.idp group by i.idi, i.nom,i.prenom" );          
            
            
            while(r.next())
            {
              ret.add(new Intervenant(r.getString(1), r.getString(2), r.getInt(3), r.getInt(4),r.getInt(5)));
            
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
        } 
        
        public static ArrayList<Intervenant> getIntervenant(int idec){
            
            ArrayList<Intervenant> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select  i.nom,i.prenom,i.idi from intervenant i, interv_ec e  "
                    +" where i.idi = e.idi and i.enservice='true' and e.idec = "+idec +" order by i.prenom");          
            
            
            while(r.next())
            {
              ret.add(new Intervenant(r.getString(1), r.getString(2), r.getInt(3)));
            
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
        }
        
        public static ArrayList<Creneau> getCreneauInterv(int idi,int annee){
            
            ArrayList<Creneau> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select ag.idp , ec.code , ec.nom , s.nom , i.prenom , i.nom , j.datee , ag.debut , ag.fin , ag.typee , ag.visible, ag.idec ,ag.numg ,ag.idcr,f.grade , f.niveau , f.typee, ag.idi ,i.mail "+
" from agenda ag , ec ec , salle s , intervenant i , jour j , promotion p, formation f "+
" where ag.idi ="+idi+" and ag.idec =  ec.idec and ag.idp =p.idp and p.idf = f.idf and p.annee="+annee+" and ag.ids = s.ids and ag.idi =i.idi and ag.idj = j.idj "+
 " ORDER BY j.datee ASC, ag.debut ASC") ;          
            
            
            while(r.next())
            {
                if(r.getString(11).equals("true")) {
                    ret.add(new Creneau(r.getInt(1),r.getString(3) , r.getString(4), r.getString(5)+" "+r.getString(6), r.getDate(7), r.getInt(8),r.getInt(9),r.getString(10),r.getInt(12),r.getInt(13),r.getInt(14),r.getString(15).substring(0, 1).toUpperCase()+String.valueOf(r.getInt(16))+r.getString(17).substring(0,1).toUpperCase(),r.getInt(18),r.getString(19)));
                }
                  
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
        } 
        
        public static ArrayList<Ec> getEC(int idf){
            
            ArrayList<Ec> ret = new ArrayList<>();
            
            
             Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select e.idec, e.nom,e.code, s.nperiode from ec e, ec_ue f,ue u ,semestre s where f.idf ="+idf+" and f.idec = e.idec and f.idue=u.idue and u.idsem = s.idsem") ;          
            
            
            while(r.next())
            {
       
     
                  ret.add(new Ec(r.getInt(1),r.getString(3)+" : "+r.getString(2)+"   S"+r.getInt(4)));
            
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
        }
        
        public static ArrayList<Ec> getEC(int idf,int periode){
            
            ArrayList<Ec> ret = new ArrayList<>();
            
            
             Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select e.idec, e.nom,e.code from ec e, ec_ue u ,semestre s, ue ue where   s.nperiode = "+periode+"and s.idf = "+idf+" and  s.idsem = ue.idsem and ue.idue = u.idue and ue.idf =u.idf and   u.idec = e.idec");          
            
            
            while(r.next())
            {
       
     
                  ret.add(new Ec(r.getInt(1),r.getString(2)));
            
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
        }
        
        public static ArrayList<Creneau> getCreneauEc(int idec,int idp){
            
            ArrayList<Creneau> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select ag.idp , ec.code , ec.nom , s.nom , i.prenom , i.nom , j.datee , ag.debut , ag.fin , ag.typee , ag.visible, ag.idec ,ag.numg ,ag.idcr,f.grade , f.niveau , f.typee, ag.idi, i.mail "+
" from agenda ag , ec ec , salle s , intervenant i , jour j ,promotion p, formation f "+
" where ag.idec="+idec+" and ag.idec =  ec.idec and ag.idp ="+idp+" and ag.idp=p.idp and p.idf=f.idf and  ag.ids = s.ids and ag.idi =i.idi and ag.idj = j.idj "+
 " ORDER BY j.datee ASC, ag.debut ASC") ;          
            
            
            while(r.next())
            {
                if(r.getString(11).equals("true")) {
                    ret.add(new Creneau(r.getInt(1),r.getString(3) , r.getString(4), r.getString(5)+" "+r.getString(6), r.getDate(7), r.getInt(8),r.getInt(9),r.getString(10),r.getInt(12),r.getInt(13),r.getInt(14),r.getString(15).substring(0, 1).toUpperCase()+String.valueOf(r.getInt(16))+r.getString(17).substring(0,1).toUpperCase(),r.getInt(18),r.getString(19)));
                }
            
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
        }        
        
        
        public static int getAnnee(int idp){
            int ret =0;
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select annee from promotion where idp="+idp) ;          
            
            
            while(r.next())
            {
                
                    ret = r.getInt(1);
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            
            return  ret;
        }
        
        public static ArrayList<Salle> getSalle(){
         
            ArrayList<Salle> ret = new ArrayList<>();
            
             Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select ids,nom from salle") ;          
            
            
            while(r.next())
            {
                
                    ret.add(new Salle(r.getInt(1),r.getString(2)));
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            
            return ret;
            
        }
        
        public static Connection getConnection() throws SQLException, InstantiationException
	{
		
	
		return EDT.api.DBStatic.getOracleConnection();
		
	}

    public static ArrayList<Integer> getAnneeMinMax() {
        ArrayList<Integer> ret = new ArrayList<>();
            
             Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select min(j.datee), max (j.datee) from agenda ag, jour j where ag.idj=j.idj") ;          
            
            
            if(r.next())
            {
                 DateFormat dateFormat = new SimpleDateFormat("yyyy");
                        
                Calendar cal = Calendar.getInstance(); 
                cal.setTime(r.getDate(1));
                ret.add(Integer.parseInt(dateFormat.format(cal.getTime())));
                
                cal.setTime(r.getDate(2));
                ret.add(Integer.parseInt(dateFormat.format(cal.getTime())));
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }

    public static ArrayList<Creneau> getCreneauSalle(int ids, int annee) {
        
        ArrayList<Creneau> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select ag.idp , ec.code , ec.nom , s.nom , i.prenom , i.nom , j.datee , ag.debut , ag.fin , ag.typee , ag.visible,ag.idec ,ag.numg ,ag.idcr, f.grade , f.niveau , f.typee, ag.ids ,i.mail "+
" from agenda ag , ec ec , salle s , intervenant i , jour j ,promotion p, formation f"+
" where ag.idp =p.idp and p.idf = f.idf and ag.ids="+ids+" and j.datee BETWEEN TO_DATE('01/09/"+annee+"', 'dd/mm/yyyy') AND TO_DATE('31/08/"+(annee+1)+"','dd/mm/yyyy') and ag.idj = j.idj and ag.idec =  ec.idec and    ag.ids = s.ids and ag.idi =i.idi  "+
 " ORDER BY j.datee ASC, ag.debut ASC") ;          
            
            
            while(r.next())
            {
                if(r.getString(11).equals("true")) {
                    ret.add(new Creneau(r.getInt(1),r.getString(3) , r.getString(4), r.getString(5)+" "+r.getString(6), r.getDate(7), r.getInt(8),r.getInt(9),r.getString(10),r.getInt(12),r.getInt(13),r.getInt(14),r.getInt(18),r.getString(15).substring(0, 1).toUpperCase()+String.valueOf(r.getInt(16))+r.getString(17).substring(0,1).toUpperCase(),r.getString(19)));
                }
            
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
    }

    public static String ajouterFormation(String grade, String niveau, String filiere, String parcours, String type, int anneeex,int periode) {
        String ret = "";
        int idc = 0;
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("select idc from contrat  where anneeex="+anneeex) ;
            
           
            if(r.next())
            {
                idc = r.getInt(1);
            
            }else{
                 
                st.executeQuery("insert into contrat values(null,"+anneeex+")");
                 
                r = st.executeQuery("select idc from contrat  where anneeex="+anneeex) ;
                 if(r.next())
                {
                idc = r.getInt(1);
            
                }
                 
            }
            
            st.executeQuery("insert into formation values(null,'"+grade+"','"
                    +niveau+"','"+filiere+"','"+parcours+"','"+type+"',"+idc+")");
           
            r = st.executeQuery("select idf from formation  where grade='"
                    +grade+"' and niveau='"+niveau+"' and filiere='"+filiere
                    +"' and parcours='"+parcours+"' and  typee='"+type
                    +"' and idcf="+idc);
                 if(r.next())
                {
                    int idf = r.getInt(1);
                    for (int i=1; i<=periode;i++){
                    st.executeQuery("insert into semestre values(null,"+idf+","+i+")");
                    }
                }
            
            
            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    public static String ajouterUe(int idFormation, String nomUE, int nperiode) {
        
        String ret = "";
        int idsem ;
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("select idsem from semestre  "
                    + "where idf="+idFormation+" and nperiode="+nperiode) ;
            
           
            if(r.next())
            {
                idsem = r.getInt(1);
                
                st.executeQuery("insert into ue values(null,"+idFormation+",'"+nomUE+"',"+idsem+")");
            
            }else{
                 
                 
                ret +="erreur  idsem non trouvée !";
            
                }
                 
            
            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    public static String ajouterEc(String nom, String code) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("insert into ec values(null,'"+nom+"','"+code+"')");
            

            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    public static ArrayList<Ue> getUe(int idf) {
        
        ArrayList<Ue> ret = new ArrayList<>();
            
            
             Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select ue.idue, ue.nom, semestre.nperiode "
                + "from ue, semestre where ue.idf ="+idf
                    +" and ue.idsem = semestre.idsem") ;          
            
            
            while(r.next())
            {
       
     
                  ret.add(new Ue(r.getInt(1),r.getString(2),r.getInt(3)));
            
            
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;

    }

    public static ArrayList<Ec> getEc() {
        
        ArrayList<Ec> ret = new ArrayList<>();
            
            
             Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select e.idec, e.nom,e.code from ec e") ;          
            
            
            while(r.next())
            {
       
                  ret.add(new Ec(r.getInt(1),r.getString(3)+" : "+r.getString(2)));     
            
            }
            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
        
    }

    public static String AffectECUE(int formation, int ec, int ue,int nbrcm,int nbrtd,double coeff) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("insert into ec_ue values("
                    +formation+","+ec+","+ue+","+nbrcm+","+nbrtd+","+coeff+")");
            

            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }
        
    
    public static String AjoutPromotion(int formation,  int annee,int nbrGp) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("insert into promotion values(null,"
                    +formation+","+annee+","+nbrGp+")");
            

            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }        

    public static String AjoutCalendrier(int annee) {
        String ret = "";
        DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        Calendar date1 = new GregorianCalendar();
        Calendar date2 = new GregorianCalendar();
        Calendar date3  = new GregorianCalendar();
        date1.set(annee,8,1);
        date2.set(annee+1,8,1);
        date3.set(annee,8,1);
         
         Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            
            
            //System.out.println("date 1 "+dateFormat.format(date1.getTime()));
            //System.out.println("date 2 "+dateFormat.format(date2.getTime()));
            //System.out.println("date 3 "+dateFormat.format(date3.getTime()));

            ResultSet r = st.executeQuery("select count(idj) from jour "
                    + "where datee BETWEEN TO_DATE('"
                    +dateFormat.format(date1.getTime())
                    +"', 'dd/mm/yyyy') AND TO_DATE('"
                    +dateFormat.format(date2.getTime())
                    +"','dd/mm/yyyy')");
            
            if (r.next() ){
                if (r.getInt(1) < 365){
                    while(date3.before(date2)){
                        int dayOfWeek = date3.get(Calendar.DAY_OF_WEEK);
                       try{ 
                            if (dayOfWeek == Calendar.SATURDAY || dayOfWeek == Calendar.SUNDAY) {
                               r = st.executeQuery("insert into jour "
                                       + "values(null,"
                                       + "TO_DATE('"
                                       +dateFormat.format(date3.getTime())
                                       +"', 'dd/mm/yyyy'),'true')");
                           }
                            else {
                               r = st.executeQuery("insert into jour values(null"
                                       + ",TO_DATE('"
                                       +dateFormat.format(date3.getTime())
                                       +"', 'dd/mm/yyyy'),'fals')");
                           }
                       } catch (SQLException ex) {
                            //Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
                            ret += ex.getMessage();
                       }
                        
                        date3.add(Calendar.DATE, 1);
                    }
                    
                    //Calendar dat = new GregorianCalendar();
                    //dat.set(annee, 9, 1);
                    Cferie fr = new Cferie(date1);
                    
                    for (Date t : fr.getLesJoursFerie() ){ 
                        if ( t.after(date1.getTime()) && t.before(date2.getTime())) {
                            r = st.executeQuery("UPDATE jour SET feriee='true' "
                                    + "WHERE datee= TO_DATE('"
                                    +dateFormat.format(t)+"', 'dd/mm/yyyy')");
                        }
                       
                    }
                    
                    //dat.set(annee+1, 9, 1);
                    fr = new Cferie(date2);
                    
                    for (Date t : fr.getLesJoursFerie() ){ 
                        if ( t.after(date1.getTime()) && t.before(date2.getTime())) {
                            r = st.executeQuery("  UPDATE jour SET feriee='true' "
                                    + "WHERE datee= TO_DATE('"
                                    +dateFormat.format(t)+"', 'dd/mm/yyyy')");
                        }
                       
                    }
                 }
                
              }
            
                if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    
}

    public static String ajouterSalle(String num, String nom, String batiment, String type) {
        
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("insert into salle values(null,"+num+""
                    + ",'"+nom+"','"+batiment+"','"+type+"')");
            

            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
        
    }

    public static String ajouterStatus(String nom, String nbrTD, String nbrSup) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("insert into statut values(null,"
                    + "'"+nom+"'" + ","+nbrTD+","+nbrSup+")");
            

            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    public static ArrayList<Status> getStatus() {
        ArrayList<Status> ret = new ArrayList<>();
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("select idstat,nom from statut");
            
            while (r.next()){
                    ret.add(new Status(r.getInt(1), r.getString(2)));
                    }

            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
           
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
           
        }
            
            return ret;
    }

    public static String ajouterIntervenant(String nom, String prenom, String mail, String tel, String pass, int status) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r = st.executeQuery("select nom from statut where idstat="+status );
            if(r.next()){
               if(r.getString(1).equals("Administrateur")) {
                    st.executeQuery("insert into intervenant values(null,'"
                        +nom+"','"+prenom+"','"+mail+"','"+pass+"','"+tel+"',"+status
                        +",'fals')");
                }
                else {
                    st.executeQuery("insert into intervenant values(null,'"
                       +nom+"','"+prenom+"','"+mail+"','"+pass+"','"+tel+"',"+status
                       +",'true')");
                }
            }
            

            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    public static String AffectEcIterv(int ec, int interv) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
                st.executeQuery("insert into interv_ec values("+interv+","+ec+")");
            


		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    public static String getAnneeMax() {
        String ret = "";
        
         Connection c;
        try {
            
            DateFormat dateFormat = new SimpleDateFormat("yyyy");
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("select max(datee) from jour");
            if (r.next()) {
                ret += dateFormat.format(r.getDate(1).getTime());
            }
            
            if (r != null) {
			r.close();
            }

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    public static String ajouterFicheVoeux(int interv, int annee, String periode, ArrayList<Integer> moment,ArrayList<Integer> poids,String valide,ArrayList<Integer> priorite) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
            
            
             Statement st = c.createStatement();
             ResultSet r = null;
            
             int i =0;
            
             if (priorite.isEmpty()) {
                for (int j =0; j<5;j++) {
                     priorite.add(Integer.valueOf(0));
                 }
            }
            while( i< moment.size()){
             //if(poids.get(i) != 0)
                
                st.executeQuery("insert into fichevoeux values("+interv+",'"
                     +periode+"',"+annee+","+(i+1)
                     +","+moment.get(i)+","+poids.get(i)+",'"+valide+"',"+priorite.get(i)+")");
             i++;
            }

            if (r != null) {
			r.close();
            }

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
    
            return ret;
    }

    public static ArrayList<Intervenant> getIntervDispo(Integer idec, String jour, int debut, int fin,int promo) {
        ArrayList<Intervenant> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            ResultSet r =st.executeQuery("select i.nom,i.prenom,i.idi from "
                    + "intervenant i, interv_ec ec "
                    +" where ec.idec = "+idec+" and ec.idi = i.idi and "
                    + "i.enservice='true' and i.idi not in "
                    + "(select idi from agenda, jour j "
                    + "where j.datee= TO_DATE('"+jour+"', 'dd/mm/yyyy') "
                    + "and j.idj = agenda.idj and  ( ("+debut+" >= debut and "+debut+" < fin)"
                    + " or ( "+debut+ " <= debut and "+fin+" > debut)) ) "
                    + " and "                
                    +" i.idi in (select f.idi from fichevoeux f, promotion p "
                    +" where f.idjour = to_char(TO_DATE('"+jour+"', 'dd/mm/yyyy'),'d') " 
                    +" and p.idp="+promo 
                    +" and f.annee = p.annee "
                    +" and f.poids > 0 "
                    +" and ( (  f.periode = 'S1' and ( extract(MONTH FROM TO_DATE('"+jour+"', 'dd/mm/yyyy')) > 8 "
                    + "               or extract(MONTH FROM TO_DATE('"+jour+"', 'dd/mm/yyyy')) = 1) )  "
                    +" or (f.periode = 'S2' and  extract(MONTH FROM  TO_DATE('"+jour+"', 'dd/mm/yyyy')) > 1 "
                    + "                and extract (MONTH FROM  TO_DATE('"+jour+"', 'dd/mm/yyyy')) < 9  ) ) "
                    +" and (("+debut+" < 1200 and (f.ncreneau =1 or f.ncreneau =3 )) or " 
                    +" ("+debut+" > 1200 and (f.ncreneau =2 or f.ncreneau =3)) ) ) " 
                    
                    
                    );          
            
            
            while(r.next())
            {
              ret.add(new Intervenant(r.getString(1), r.getString(2), r.getInt(3)));
            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
    }

    public static ArrayList<Salle> getSalleDispo(String type, String jour, int debut, int fin) {
        ArrayList<Salle> ret = new ArrayList<>();
            
            Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
            /*ResultSet r =st.executeQuery("select ids, nom from salle "
                    +" where typee='"+type+"' and ids not in (select ids from agenda, jour j where j.datee= TO_DATE('"+jour+"', 'dd/mm/yyyy') and j.idj = agenda.idj and ( ("+debut+" >= debut and "+debut+" < fin) or ( "+debut+ " <= debut and "+fin+" > debut)) ) " );          
            */
             
            ResultSet r =st.executeQuery("select ids, nom from salle "
                    +" where ids not in (select ids from agenda, jour j where "
                    + "j.datee= TO_DATE('"+jour+"', 'dd/mm/yyyy') and "
                    + "j.idj = agenda.idj and ( ("+debut+" >= debut and "+debut+" < fin)"
                    + " or ( "+debut+ " <= debut and "+fin+" > debut)) ) order by nom " );          
            
            while(r.next())
            {
              ret.add(new Salle(r.getInt(1), r.getString(2)));

            }
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
    }

    static String ajouterUnCreneau(Integer idCr, int salle, int idp, int ec, int interv, String jour, int gp, String type, int debut, int fin,String valide,String visible) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
            //Vérifier si la clé appartient à l'intervenant !
            int idj ;
             Statement st = c.createStatement();
             ResultSet r=st.executeQuery("select idj from jour where datee = "
                     + "TO_DATE('"+jour+"', 'dd/mm/yyyy')");
             if (r.next()){
                 idj = r.getInt(1);     
             //ret += "(idj="+idj+")";
             
             st.executeQuery("insert into agenda values("+idCr+","+idp+","+salle+","
                     +ec+","+interv+","+idj+","+debut+","+fin+","+gp+",'"+type+
                     "','"+valide+"','"+visible+"')");
             }else{
                 ret += " erreur id jour n'est pas trouvé le créneau n'est "
                         + "pas ajouté !";
             }                  
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            //if(ex.getErrorCode() == 20324)
            ret += ex.getMessage().substring(10, ex.getMessage().length()).replaceAll("ORA-.*", "");
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }
    
    static String ajouterDesCreneaux(int salle, int idp, int ec, int interv, TreeMap<Integer,String> lesJours, int gp, String type, int debut, int fin,String visible) {
        String ret = "";
        String hdebut;
        String hfin;
        int nbrCrAjout = 0;
        
        Connection c;
         
        try {
            c = getConnection();
            
           
             Statement st = c.createStatement();
             ResultSet r =null;  
             
             //Vérification da la disponiblilité de l'enseignant et la salle
             for (int idjour : lesJours.keySet()){
                 
             //vérifivation de la disponibilité de l'enseignant    
                r = st.executeQuery("select i.idi,i.prenom,i.nom from intervenant i "
                    +" where i.idi = "+interv+" and i.enservice='true' and i.idi "
                     + "not in (select idi from agenda where idj="+idjour+" "
                     + "and  ( ("+debut+" >= debut and "+debut+" < fin) "
                     + "or ( "+debut+ " <= debut and "+fin+" > debut)) ) " );  
             
             if (!r.next()){
                hdebut= String.valueOf(debut).replace(String.valueOf(debut).substring(0, String.valueOf(debut).length()-2), String.valueOf(debut).substring(0, String.valueOf(debut).length()-2)+":");
                hfin=   String.valueOf(fin).replace(String.valueOf(fin).substring(0, String.valueOf(fin).length()-2), String.valueOf(fin).substring(0, String.valueOf(fin).length()-2)+":");
                //ret += " erreur l'enseignant :"+r.getString(2)+" "+ r.getString(3) +" n'est pas disponible le "+lesJours.get(idjour) +" de "+hdebut+" à "+hfin+" ! \n";
                ret += " erreur l'enseignant n'est pas disponible le "+lesJours.get(idjour) +" de "+hdebut+" à "+hfin+" ! \n";
                ret += System.getProperty("line.separator"); 
             }
             
             if (r != null) {
		r.close();
            }
             
           //vérifivation de la disponibilité de la salle 
             r =st.executeQuery("select ids, nom from salle "
                    +" where ids="+salle+" and ids not in (select ids from agenda"
                     + " where agenda.idj= "+idjour+" and ( ("+debut+" >= debut "
                     + "and "+debut+" < fin) or ( "+debut+ " <= debut "
                     + "and "+fin+" > debut)) ) " );          
            
             if (!r.next()){
                hdebut= String.valueOf(debut).replace(String.valueOf(debut).substring(0, String.valueOf(debut).length()-2), String.valueOf(debut).substring(0, String.valueOf(debut).length()-2)+":");
                hfin=   String.valueOf(fin).replace(String.valueOf(fin).substring(0, String.valueOf(fin).length()-2), String.valueOf(fin).substring(0, String.valueOf(fin).length()-2)+":");
                //ret += " erreur la salle "+r.getString(2) +" n'est pas disponible le "+lesJours.get(idjour) +" de "+hdebut+" à "+hfin+" ! \n";  
                ret += " erreur la salle n'est pas disponible le "+lesJours.get(idjour) +" de "+hdebut+" à "+hfin+" ! ";
                ret += System.getProperty("line.separator"); 
             }
             
             if (r != null) {
			r.close();
             }
             
            }
             
             //Dans le cas ou les l'enseignant et la salle sont libre pour tous les créneaux j'insère dans la table agenda
            
             if(ret.equals("")){
               for (int idjour : lesJours.keySet()) {
                     r = st.executeQuery("insert into agenda values(null,"+idp+","+salle+","+ec+","+interv+","+idjour+","+debut+","+fin+","+gp+",'"+type+"','true','"+visible+"')");
                     nbrCrAjout++;
               }        
             }
                if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
             
        } catch (SQLException | InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret += nbrCrAjout +"/"+ lesJours.size()+" créneaux ont été ajouté avec succés ";
            
            ret += ex.getMessage().substring(10, ex.getMessage().length()).replaceAll("ORA-.*", "");
            
            
            //ret += ex.getMessage();
        }
            
            return ret;
    }
    
    
    public static int getNbrGp(int idp) {
        int ret = 0;
        
         Connection c;
        try {
            c = getConnection();
            
            //Vérifier si la clé appartient à l'intervenant !
            
             Statement st = c.createStatement();
             ResultSet r=st.executeQuery("select nbrgrp from promotion where idp = "+idp);
             if (r.next()) {
                ret = r.getInt(1);
            }
                           
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
        }
            
            return ret;
    }
    
    
    public static Date getJourMax() {
        Date ret = new Date();
        
         Connection c;
        try {
            
           
            c = getConnection();
            
             Statement st = c.createStatement();
            ResultSet r=st.executeQuery("select max(datee) from jour");
            if (r.next()) {
                ret = r.getDate(1);
            }
            
            if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
        }
            
            return ret;
    }

    
    public static String ajouterJourFerie(String date,String option) {
        String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             ResultSet r=st.executeQuery("UPDATE jour SET feriee='"+option+"' WHERE datee= TO_DATE('"+date+"','dd/mm/yyyy')");
                      
                if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    static String SupprimerCr(String[] options) {
         String ret = "";
        
         
         
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
             ResultSet r = null;
             for (String i : options) {
                st.executeQuery("delete agenda where idcr="+Integer.parseInt(i));
            }
                     
            

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            //if(ex.getErrorCode() == 20324)
            ret += ex.getMessage().substring(10, ex.getMessage().length()).replaceAll("ORA-.*", "");
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }
    
    
    static String getJourferie(int annee) {
          //String ret ="[";
        String ret ="";
        
         DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
         DateFormat dateff= new SimpleDateFormat("M-d-yyyy");
        Calendar date1 = new GregorianCalendar();
        Calendar date2 = new GregorianCalendar();
        Calendar date3  = new GregorianCalendar();
        date1.set(annee,8,1);
        date2.set(annee+1,8,1);
        date3.set(annee,8,1);
         
         Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            
           ResultSet r = st.executeQuery("select datee from jour where feriee='true' and datee BETWEEN TO_DATE('"+dateFormat.format(date1.getTime())+"', 'dd/mm/yyyy') AND TO_DATE('"+dateFormat.format(date2.getTime())+"','dd/mm/yyyy')");
            
            while (r.next() ){
                
                ret+= dateff.format(r.getDate(1))+",";
            }     
            
            ret = ret.substring(0, ret.length()-1);
                if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
           
        }
            
            return ret;
    }
    
    static String getJourferie(int debut, int fin) {
        String ret ="";
        
         DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
         DateFormat dateff= new SimpleDateFormat("M-d-yyyy");
        Calendar date1 = new GregorianCalendar();
        Calendar date2 = new GregorianCalendar();
        Calendar date3  = new GregorianCalendar();
        date1.set(debut-1,8,1);
        date2.set(fin,8,1);
        date3.set(debut,8,1);
         
         Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            
           ResultSet r = st.executeQuery("select datee from jour where feriee='true' and datee BETWEEN TO_DATE('"+dateFormat.format(date1.getTime())+"', 'dd/mm/yyyy') AND TO_DATE('"+dateFormat.format(date2.getTime())+"','dd/mm/yyyy')");
            
            while (r.next() ){
                
                ret+= dateff.format(r.getDate(1))+",";
            }     
            
            ret = ret.substring(0, ret.length()-1);
                if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
           
        }
            
            return ret;
    }

    static TreeMap<Integer,String> getJoursFrom(String jour,int nbrCr) {
        
        TreeMap<Integer,String> ret =new TreeMap<>();
        
         DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
         
        
         
         Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            
          
            ResultSet r = st.executeQuery("SELECT * FROM (SELECT idj,datee FROM jour WHERE feriee='fals' and datee >= TO_DATE('"+jour+"','dd/mm/yyyy') and to_char(datee,'d') = to_char(TO_DATE('"+jour+"','dd/mm/yyyy'),'d') ORDER BY datee ) WHERE ROWNUM <="+nbrCr );
            
            while (r.next() ){
                
                ret.put(r.getInt(1),dateFormat.format(r.getDate(2)));
            }     
            
            
                if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
           
        }
            
            return ret;
    }

    static String getFormModifFichVoeux(int interv, int annne, String periode,Utilisateur user) {
        String form ="";
        
        FicheVoeux fVoeux = new FicheVoeux(annne, periode);
        
         Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            
          
            ResultSet r = st.executeQuery("SELECT idjour,ncreneau,poids,valide,priorite "
                    + "FROM fichevoeux where idi="+interv+" and annee="+annne
                    + " and periode='"+periode+"'");
            
            while (r.next() ){
                
                fVoeux.addVoeux(r.getInt(1),new Voeux(r.getInt(2),r.getInt(3),r.getString(4),r.getInt(5),user));
            }     
            
            
                if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
           
        }
        
         DateFormat dateFormat = new SimpleDateFormat("yyyy");
        Calendar cal = Calendar.getInstance(); 
        cal.setTime(new Date()); 
        
         cal.setFirstDayOfWeek(6);
        
         form += "<table class='calendrier calendrier-etroit'> <caption> <span>Les souhaits : </span></caption>";
        
        while (cal.getTime().getDay() != Calendar.SUNDAY) {
            cal.add(Calendar.DATE, 1);
        }
        
        DateFormat formater = new SimpleDateFormat("EEEE");
//System.out.println(formater.format(aujourdhui));
        
        //DateFormat fullDateFormat = DateFormat.getDateTimeInstance(DateFormat.FULL,DateFormat.FULL);
        for (int i =1; i<= 5; i++){
            form += "<tr><th>Jours</th><th>La periode / jour</th><th>Poids</th>";
            if(user instanceof Administrateur) {
                form += "<th>Priorité</th>";
            }  
            form += "</tr>";
            
            form += "<tr><td>"+formater.format(cal.getTime())+"</td>";
            if(fVoeux.getJoursVoeux().get(i) == null){
                form += "<td><input type='radio' name='moment"+i+"' value='3' checked>Toute la journée<br>";
                form += "<input type='radio' name='moment"+i+"' value='1'>le matin<br/>";
                form += "<input type='radio' name='moment"+i+"' value='2'>Après midi</td>";
                
                form += "<td>";
                
                form += "<input type='radio' name='poids"+i+"' value='4' checked>Je le veux<br>";
                form += "<input type='radio' name='poids"+i+"' value='3'>Oui<br>";
                form += "<input type='radio' name='poids"+i+"' value='2'>Possible<br>";
                form += "<input type='radio' name='poids"+i+"' value='1'>Très difficile<br>";
                form += "<input type='radio' name='poids"+i+"' value='0'>Pas possible</td>";
                
                if(user instanceof Administrateur){
                    form += "<td>";
                    form += "<select name='priorite"+i+"'>";
                    for(int j=0;j<= 10; j++) {
                        form  +="<option value='"+j+"'>"+j+"</option>";
                    }        
                    form += "</select>";
                    form += "<br></td>";
                }
            }else{
                
                form += fVoeux.getJoursVoeux().get(i).getBaliseCreneau(i);
                
                }
       
            form +="</tr>";
            cal.add(Calendar.DATE, 1);
        }
                
            form += "</table><br/>";
            

             if(fVoeux.getJoursVoeux().get(1) == null ){
                 form += " <input type='checkbox' "
                    + "name='valide' checked>Valider définitivement mes souhaits<br/><br/>";
                 form +="<input name='requette' type='hidden' value='ajout'/>";
                  form +="<input type='submit' value='Enregistrer'/><br/>";
             }else
         
                if(fVoeux.getJoursVoeux().get(1).isValide().equals("true") &&  user instanceof IntervUsers){
                    
                    form += " <input type='checkbox' "
                    + "name='valide' checked disabled>Valider définitivement mes souhaits<br/><br/>";
                        form +="<input name='requette' type='hidden' value='modif'/>";
                    form +="<input type='submit' value='Enregistrer' disabled/><br/>";
                }else{
                    form += " <input type='checkbox' "
                    + "name='valide' checked> Valider définitivement mes souhaits<br/><br/>";
                        form +="<input name='requette' type='hidden' value='modif'/>";
                        form +="<input type='submit' value='Enregistrer'/><br/>";
                    }
            return form;
    }

    
    
    static String modifierFicheVoeux(int interv, int annee, String periode, ArrayList<Integer> moment, ArrayList<Integer> poids,String valide,ArrayList<Integer> priorite) {
       String ret = "";
        
        String form ="";
        
        FicheVoeux fVoeux = new FicheVoeux(annee, periode);
        
         Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            ResultSet r  = null;
          // Vérification de l'existance des créneaux qui sont programée lorsque
          // l'intevenant n'est pas disponible selon la nouvelle fiche de voeux
            DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
            ArrayList<String> conflits = new ArrayList<>();
            String hdebut;
            String hfin;
            int i =0;
            
            while( i< moment.size()){
                
              r = st.executeQuery("select j.datee,ag.debut,ag.fin "
                + " from agenda ag, jour j, promotion p"
                 +" where ag.idi = "+interv+" and ag.idj = j.idj"
                 +" and (("+poids.get(i)+"=0 and "+moment.get(i) +" =3) or ("+moment.get(i)+"<>3 and "+poids.get(i)+">0)) "    
                 +" and to_char(j.datee,'d')= "+(i+1)
                 +"   and ag.idp = p.idp"
                  +"  and p.annee = "+annee
                  +"  and ( (  '"+periode+"' = 'S1' and ( extract(MONTH FROM  j.datee) > 8 or extract(MONTH FROM  j.datee)=1))"  
                  +"  or ('"+periode+"' = 'S2' and  extract(MONTH FROM  j.datee) > 1 and extract(MONTH FROM  j.datee) < 9  ) )"
                  +"  and ((ag.debut < 1200 and ("+moment.get(i) +" =2 or "+moment.get(i) +" =3)) or" 
                          +"  (ag.debut >= 1200 and ("+moment.get(i) +" =1 or "+moment.get(i)+" =3)) )");
   
                 while (r.next()){
                    hdebut= String.valueOf(r.getInt(2)).replace(String.valueOf(r.getInt(2)).substring(0, String.valueOf(r.getInt(2)).length()-2), String.valueOf(r.getInt(2)).substring(0, String.valueOf(r.getInt(2)).length()-2)+":");
                    hfin=   String.valueOf(r.getInt(3)).replace(String.valueOf(r.getInt(3)).substring(0, String.valueOf(r.getInt(3)).length()-2), String.valueOf(r.getInt(3)).substring(0, String.valueOf(r.getInt(3)).length()-2)+":"); 
                    conflits.add("erreur il existe un créneau le "
                        +dateFormat.format(r.getDate(1).getTime())+" de "
                        +hdebut+" à "+hfin);
                
                }
                 i++;
             
            }
            
            if (conflits.isEmpty()){
                i=0;
            
                if(priorite.isEmpty()) {
                    while( i< moment.size()){
                    r = st.executeQuery("update fichevoeux set ncreneau="
                        +moment.get(i)+", poids="+poids.get(i)+",valide='"+valide+"' where "
                        + "idi="+interv+" and annee="+annee+ " and periode='"
                        +periode+"' and idjour="+(i+1));
                    i++;
                    }
                }
                
                else {
                    while( i< moment.size()){
                    r = st.executeQuery("update fichevoeux set ncreneau="
                        +moment.get(i)+", poids="+poids.get(i)+",valide='"+valide+"',priorite="+priorite.get(i)+" where "
                        + "idi="+interv+" and annee="+annee+ " and periode='"
                        +periode+"' and idjour="+(i+1));
                    i++;
                    }
                }
            }else{
                for (String err : conflits) {
                    ret += err.concat(System.getProperty("line.separator") );
                }
                
            }
            
            
            
                if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
       
        } catch (SQLException | InstantiationException ex ) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
            
        }
    
            return ret;
    
    }
    
    
    
    public static String AjouterFicheVoeuxParDefault(int annee) {
        String ret = "";
       
        int i =0;
       ArrayList<Integer> moment = new ArrayList<>();
       ArrayList<Integer> poids = new ArrayList<>();
       while(i<5){
        moment.add(3);
        poids.add(2);
        i++;
        }
               
            ArrayList<Intervenant> interv = getAllIntervenant();
            
              Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            
          
            ResultSet r = null; 

            for (Intervenant ens :interv ){
                
                r = st.executeQuery("SELECT count(idi) "
                    + "FROM fichevoeux "
                        + "where idi="+ens.getId()
                        +" and annee="+annee
                    + " and (periode='S1' or periode='S2') ");
            
                while (r.next() ){
                    if(r.getInt(1)==0){
                        ret += ajouterFicheVoeux(ens.getId(), annee, "S1", 
                                moment, poids,"fals",new ArrayList<Integer>());
                        
                        ret += ajouterFicheVoeux(ens.getId(), annee, "S2", 
                                moment, poids,"fals",new ArrayList<Integer>());
                    }
                        
                }     
                
            }
        
            
             if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
            } catch (SQLException | InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret += ex.getMessage();
            
        }
            
            return ret;
    
}
    
     public static TreeMap<String,PlaquetteForm> getPlaquette(int promo,int grp) {
        
        
        TreeMap<String,PlaquetteForm> ret = new TreeMap<>();
         int i=1;   
              Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            
     /*     
     ResultSet r = st.executeQuery("Select ag.idec,S.Nperiode,Ag.Typee, "
       + "Ue.Nom,Ec.Nom,Ecue.Nbrcm,Ecue.Nbrtd,Ecue.Coeff,count(*) ,"
       + "sum(((TRUNC(ag.fin/100)*60)+MOD(ag.fin,100)) "
             +" - ((TRUNC(ag.debut/100)*60)+MOD(ag.debut,100)))/60 " 
+" From Promotion P, Agenda Ag, Semestre S , Ue Ue, Ec Ec, Ec_Ue Ecue "
+" Where Ag.Idp="+promo+" and (Ag.numG="+grp+" or ag.typee = 'cm') And Ag.Idec=Ec.Idec "
+" And P.Idp = Ag.Idp And P.Idf=Ecue.Idf And Ec.Idec=Ecue.Idec And "
+" Ecue.Idue=Ue.Idue And Ue.Idf =P.Idf And Ue.Idsem=S.Idsem And S.Idf=P.Idf "
+" Group By Ag.Idec,S.Nperiode,Ag.Typee,Ue.Nom,Ec.Nom,"
            + " Ecue.Nbrcm,Ecue.Nbrtd,Ecue.Coeff "
+" Order by s.nperiode,Ue.nom,ec.nom,ag.typee");
       */
       ResultSet r = st.executeQuery("Select S.Nperiode, Ue.Nom,Ec.Nom,Ec.Idec,"
 +" Ecue.Nbrcm,Ecue.Nbrtd,Ecue.Coeff  "
 +" From Promotion P, Semestre S , Ue Ue, Ec Ec, Ec_Ue Ecue" 
 +" Where P.Idp="+promo+" And P.Idf=Ecue.Idf And Ec.Idec=Ecue.Idec And "
 +" Ecue.Idue=Ue.Idue And Ue.Idf =P.Idf And Ue.Idsem=S.Idsem And S.Idf=P.Idf "
 +" Order by s.nperiode,Ue.nom,ec.nom");    
            
            while (r.next() ){
               if(ret.get(r.getInt(1)+r.getString(2)+r.getString(3)+r.getInt(4))==null) {
                    ret.put(r.getInt(1)+r.getString(2)+r.getString(3)+r.getString(4),
                            new PlaquetteForm(r.getInt(4), r.getInt(1),
                                               r.getString(2), r.getString(3),r.getDouble(5),r.getDouble(6), r.getInt(7)));
                }       
            } 
            
          for (String cle : ret.keySet()){  
              
      r = st.executeQuery("Select ag.idec,Ag.Typee,count(*) ,"
       + "sum(((TRUNC(ag.fin/100)*60)+MOD(ag.fin,100)) "
       + " - ((TRUNC(ag.debut/100)*60)+MOD(ag.debut,100)))/60 " 
+" From Agenda Ag "
+" Where Ag.Idp="+promo+" and (Ag.numG="+grp+" or ag.typee = 'cm') "
+" And Ag.Idec="+ret.get(cle).getIdec()
+" Group By Ag.Idec,Ag.Typee "
+" Order by ag.idec,ag.typee ");
            
        
             while (r.next() ){
                 
                   if(r.getString(2).equals("cm")){
                       ret.get(cle).setNbrHCM(r.getDouble(4));
                       //ret.get(r.getInt(1)).setNbrHCMTotal(r.getDouble(6));
                       ret.get(cle).setNbrSeanceCM(r.getInt(3));
                   }
                   
                   if(r.getString(2).equals("td")){
                       ret.get(cle).setNbrHTD(r.getDouble(4));
                       //ret.get(r.getInt(1)).setNbrHTDTMETotal(r.getDouble(7));
                       ret.get(cle).setNbrSeanceTD(r.getInt(3));
                   }
                   
                   if(r.getString(2).equals("tp")){
                       ret.get(cle).setNbrHTME(r.getDouble(4));
                       //ret.get(r.getInt(1)).setNbrHTDTMETotal(r.getDouble(7));
                       ret.get(cle).setNbrSeanceTME(r.getInt(3));
                   }
             }  
                     
        }     
            
        
            
             if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
            } catch (SQLException | InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
           // ret += ex.getMessage();
            
        }
            
            return ret;
        
    }
    
    public static String AjoutDatePeriodes
           (int formation,int annee,String[] periodeStart,String[] periodeend) {
        
        String ret="";
        
         Connection c;
        try {
            
            int idp;
            ArrayList<Integer> idsem = new ArrayList<>(); 
            c = getConnection();
            
            Statement st = c.createStatement();
            
            
            ResultSet r = st.executeQuery("select idp from promotion where idf="
                    +formation+" and annee="+annee);
            
            if (r.next() ){
                 idp = r.getInt(1);
                r = st.executeQuery("select idsem from semestre where idf="
                    +formation+" order by nperiode");
                 while(r.next()) {
                    idsem.add(r.getInt(1));
                }
                 
                 if (idsem.size() != periodeStart.length) {
                    ret +="Erreur dans au niveau du nombre des semestre lié à la formation";
                }
                 else{
                     for (int i=0; i<idsem.size();i++ ) {
                         r = st.executeQuery("insert into periode values("
                                 +idp+","+idsem.get(i)+",to_date('"
                                 +periodeStart[i]+"','dd/mm/yyyy'),to_date('"
                                 +periodeend[i]  +"','dd/mm/yyyy'))");
                     }
                 }
                     
                 
            }else {
                ret += " Erreur la promotion n'a pas été ajouté correctement ! ";
            }        
                
            
        
            
             if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
            } catch (SQLException | InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
           // ret += ex.getMessage();
            
        }
            
        
        
        return ret;
        
    }

    static String ActvDesactvCmptInterv(int interv, String etat) {
         String ret = "";
        
         Connection c;
        try {
            c = getConnection();
            Statement st = c.createStatement();
            ResultSet r;
            if(etat.equals("enable")) {
                st.executeQuery("update intervenant set enservice='true' where idi ="+interv);
            }
            else {
                st.executeQuery("update intervenant set enservice='fals' where idi ="+interv);
            }

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    static TreeMap<String,PlaquetteInterv> getPlaquetteIterv(int annee) {
       
        TreeMap<String,PlaquetteInterv> ret = new TreeMap<>();
           
              Connection c;
        try {
            c = getConnection();
            
            Statement st = c.createStatement();
            
          
     ResultSet r = st.executeQuery("Select I.Idi,I.Prenom,I.Nom, St.Nbrh, "
             + " St.Nbrhsup,St.Nom "
+" From Intervenant I, Statut St"
+" where i.idstat=st.idstat "
+" Group By I.Idi,I.Prenom,I.Nom, St.Nbrh, St.Nbrhsup,St.Nom"
+" order by I.prenom");
            
            while (r.next() ){
                  
               ret.put(r.getString(2)+" "+ 
                  r.getString(3),new PlaquetteInterv(r.getInt(1),r.getString(2), 
                  r.getString(3),0 , r.getInt(4),r.getInt(5), r.getString(6)));       
            }
            
for (String cle : ret.keySet()){                
 r = st.executeQuery("Select Sum(((Trunc(Ag.Fin/100)*60)+Mod(Ag.Fin,100)) - "
 + "((Trunc(Ag.Debut/100)*60)+Mod(Ag.Debut,100)))/60 "
+" From agenda ag, promotion p"
+" where ag.idi="+ret.get(cle).getIdi()+" and ag.idp=p.idp and p.annee="+annee);
      if(r.next()) {
        ret.get(cle).setNbrHTrv(r.getInt(1));
    }
}
                if (r != null) {
			r.close();
                }

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
            
            } catch (SQLException | InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
           // ret += ex.getMessage();
            
        }
            
            return ret;
        
    }

    
    static ArrayList<Creneau> getInfoCreneau(String[] options) {
        ArrayList<Creneau> ret = new ArrayList<>();
        
         Connection c;
        try {
            c = getConnection();
            Statement st = c.createStatement();
            ResultSet r=null;
            
            for (String idcr: options){
                r = st.executeQuery("Select ag.idcr,ag.idp,ag.ids,ag.idec,ag.idi,"
                    + " ag.idj,ag.debut,ag.fin,ag.numG,ag.typee,ag.valide,ag.visible,j.datee "
                    + " from  agenda ag,jour j "
                    + " where ag.idcr="+idcr+" and ag.idj=j.idj ");
            if(r.next()) {
                    ret.add(new Creneau(r.getInt(1),r.getInt(2),r.getInt(3),r.getInt(4),
                            r.getInt(5),r.getInt(6),r.getInt(7),r.getInt(8),r.getInt(9),
                            r.getString(10),r.getString(11),r.getString(12),r.getDate(13)));
                }
            }
            
            if (r != null) {
		r.close();
            }

            if (st != null) {
		st.close();
            }

            if (c != null) {
		c.close();
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);

        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }
            
            return ret;
    
        
    }

    static String getDateOfPeriode(int idp, int periode) {
         String ret = "";
        
         DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
         
         Connection c;
        try {
            c = getConnection();
            
             Statement st = c.createStatement();
             
             ResultSet r = st.executeQuery("select p.debut,p.fin "
                 + " from periode p,semestre s "
                 + " where p.idp="+idp+" and p.idsem=s.idsem  and s.nperiode="+periode); 
             if(r.next()) {
                ret += dateFormat.format(r.getDate(1)) +" et "+ dateFormat.format(r.getDate(2));
            }
             
             if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
           
        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret +=ex.getMessage();
        }
            
            return ret;
    }

    static String getNomFormation(int idp) {
         
                String ret="";
            try {
               
               
                
            Connection c = getConnection();
            Statement st = c.createStatement();
            
            
            ResultSet r =st.executeQuery("select f.idf,f.grade,f.niveau,"
                    + "f.filiere,f.parcours,f.typee "
                    + "from formation f,promotion p "
                    + "where p.idp="+idp+ " and p.idf = f.idf " );          
            
            
            if(r.next())
            {
			ret += r.getString(2)+" NIVEAU "+r.getString(3)+" "+r.getString(4)+" "+r.getString(5)+" "+r.getString(6);	
            }
		
            
           
            
		if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}

        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        }

           
            return ret;
        }
    
    
    

    static Session connexion(String email, String pass) {
            Session ret;
            String key;
            
        int min = 20;	
	long dateCurent = System.currentTimeMillis();
        Timestamp original = new Timestamp(dateCurent);
        
        Calendar cal = Calendar.getInstance();
        cal.setTimeInMillis(original.getTime());
        cal.add(Calendar.MINUTE, min);
        
        Timestamp expire = new Timestamp(cal.getTime().getTime());
            
        try {
               
               
                
            Connection c = getConnection();
            Statement st = c.createStatement();
            
            
            ResultSet r =st.executeQuery("select i.idi,i.prenom,i.nom,i.passwd, st.nom "
                    + " from intervenant i, Statut st "
                    + " where mail='"+email+"' and i.idstat = st.idstat");          
            
            
            if(r.next())
            {
		if(r.getString(4).equals(pass)){
                    
                    supprKeyExpire();
                    
                    key = API.generateKey();
                    Statement ps = c.createStatement();
                    ps.executeQuery("delete from sessions where idi = " + r.getInt(1));
                    
                    PreparedStatement pst = c.prepareStatement(
                            "insert into sessions values(?,?,?)");
                    pst.setInt(1, r.getInt(1));
                    pst.setString(2, key);
                    pst.setTimestamp(3, expire);
                    
                    pst.executeUpdate();
                    
                    ret = new Session(key, r.getString(3), r.getString(2),
                            r.getInt(1),"",r.getString(5));
                    
                    
                }else{
                    ret= new Session("Le mot de passe saisie est incorrect !");
                }
                        		
            }else{
                
                ret= new Session("L'email saisie est incorrect !");
            }
		
            
           
            
		if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}

        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret = new Session("Problème de connexion veillez réessayer  !");
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret = new Session("Problème de connexion veillez réessayer  !");
        }

           
            return ret;
        
    }
    
    static void supprKeyExpire()
	{ 
		long retryDate = System.currentTimeMillis();
                    Timestamp original = new Timestamp(retryDate);
		
		Connection c;
		try {
			c = getConnection();
			
			PreparedStatement st = c.prepareStatement("delete "
                                + "from sessions where expire < ?");
			st.setTimestamp(1, original);
			st.executeUpdate();
			
			if (st != null) {
				st.close();
			}

			if (c != null) {
				c.close();
			}
			
			
		} catch (SQLException e) {
                    
		} catch (InstantiationException ex) {
                    
		}
		
	}
    
    static void deconnect(String key)
	{ 
		
		
		Connection c;
		try {
			c = getConnection();
			
                         Statement st = c.createStatement();

                         
			ResultSet r =st.executeQuery("delete "
                                + "from sessions where keys='"+key+"'");

			if (st != null) {
				st.close();
			}

			if (c != null) {
				c.close();
			}
			
			
		} catch (SQLException e) {
                    
		} catch (InstantiationException e) {
                    
		}
		
	}

    static boolean isKeyValid(String key) {
        supprKeyExpire();
        
        boolean ret = false;
          try {
               
               
                
            Connection c = getConnection();
            Statement st = c.createStatement();
            
            
            ResultSet r =st.executeQuery("select st.nom, s.keys from "
                    + "sessions s, statut st, intervenant i where "
                    + "s.keys='"+key+"' and s.idi=i.idi and i.idstat = st.idstat "
                    + "and st.nom='Administrateur' ");          
            
            
            if(r.next())
            {   
                    ret= true;
                    
                    int min = 20;	
                    long dateCurent = System.currentTimeMillis();
                    Timestamp original = new Timestamp(dateCurent);
        
                    Calendar cal = Calendar.getInstance();
                    cal.setTimeInMillis(original.getTime());
                    cal.add(Calendar.MINUTE, min);
        
                    Timestamp expire = new Timestamp(cal.getTime().getTime());
                    
                    PreparedStatement pst = c.prepareStatement(
                            "update sessions set expire=? where keys='"+key+"'");
                    pst.setTimestamp(1, expire);
                    
                    pst.executeUpdate();
                    
            }
            
           
            
		if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}

        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);

        }

           
            return ret;
        
    }

    static boolean isKeyValidInterv(String key, Integer interv) {
        supprKeyExpire();
        
        boolean ret = false;
        Connection c =null;
        Statement st=null;
        ResultSet r=null;
        
          try {
               
               
                
           c  = getConnection();
           st = c.createStatement();
            
            
           r =st.executeQuery("select st.nom, s.keys from "
                    + "sessions s, statut st, intervenant i where "
                    + "s.keys='"+key+"' and s.idi= "+interv+" and s.idi=i.idi "
                    + "and i.idstat = st.idstat ");          
            
            
            if(r.next())
            {   
                    ret= true;
                    
                    int min = 20;	
                    long dateCurent = System.currentTimeMillis();
                    Timestamp original = new Timestamp(dateCurent);
        
                    Calendar cal = Calendar.getInstance();
                    cal.setTimeInMillis(original.getTime());
                    cal.add(Calendar.MINUTE, min);
        
                    Timestamp expire = new Timestamp(cal.getTime().getTime());
                    
                    PreparedStatement pst = c.prepareStatement(
                            "update sessions set expire=? where keys='"+key+"'");
                    pst.setTimestamp(1, expire);
                    
                    pst.executeUpdate();
                    
            }
            
           
            
		

        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);

        }
          
          
          try{
              if (r != null) {
            
                r.close();
           
                
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
          }catch (SQLException ex){
              Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
    
        }

           
            return ret;
    }

    
    static FormationConsult getFormation(int idf) {
        
        FormationConsult ret=null;
        Connection c =null;
        Statement st=null;
        ResultSet r=null;
        
          try {
           
           String nom ;
           int anneeEx;
           ArrayList<Integer> promo=new ArrayList<>();
           int nbrPeriode;
           TreeMap<String,PlaquetteForm> lesUeUc = new TreeMap<>();
                
           c  = getConnection();
           st = c.createStatement();
            
            
           r =st.executeQuery("select f.grade,f.niveau,f.filiere,f.parcours,"
                   +" f.typee,c.anneeEx,count(s.idsem) "
                   +" from formation f, contrat c, semestre s  "
                   +" where f.idf="+idf+" and f.idcf=c.idc and s.idf=f.idf "
                   +" Group By F.Grade,F.Niveau,F.Filiere,F.Parcours,"
                   +" F.Typee,C.Anneeex ");          
            
            
            if(r.next())
            {   
                 nom = r.getString(1)+" Niveau "+r.getString(2)+" "+r.getString(3)
                         +" "+r.getString(4)+" "+r.getString(5);
                 anneeEx =r.getInt(6);
                 nbrPeriode=r.getInt(7); 
                 
                 r =st.executeQuery("select annee from promotion where idf="
                         +idf+" order by annee");
                 while(r.next())
                    {
                       promo.add(r.getInt(1)); 
                    }
                 
                 
                 r = st.executeQuery("Select S.Nperiode, Ue.Nom,Ec.Nom,Ec.Idec,"
                +" Ecue.Nbrcm,Ecue.Nbrtd,Ecue.Coeff  "
                +" From Semestre S , Ue Ue, Ec Ec, Ec_Ue Ecue" 
                +" Where Ecue.Idf="+idf+" And Ec.Idec=Ecue.Idec And "
                +" Ecue.Idue=Ue.Idue And Ue.Idf ="+idf
                +" And Ue.Idsem=S.Idsem And S.Idf="+idf
                +" Order by s.nperiode,Ue.nom,ec.nom");    
            
                while (r.next() ){
                    
                lesUeUc.put(r.getInt(1)+r.getString(2)+r.getString(3)+r.getString(4),
                new PlaquetteForm(r.getInt(4), r.getInt(1),
                     r.getString(2), r.getString(3),r.getDouble(5),r.getDouble(6), r.getDouble(7)));       
                }
                 
                ret = new FormationConsult(nom, anneeEx, nbrPeriode, promo, lesUeUc);
                 
            }
            
           
            
		

        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
        
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);

        }
          
          
          try{
              if (r != null) {
            
                r.close();
           
                
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}
          }catch (SQLException ex){
              Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
    
        }

           
            return ret;
    }

    static String modifierPasswd(int interv, String passold, String pass) {
         String ret= "";
           
            
        try {
               
               
                
            Connection c = getConnection();
            Statement st = c.createStatement();
            
            
            ResultSet r =st.executeQuery("select i.passwd "
                    + " from intervenant i "
                    + " where i.idi="+interv);          
            
            
            if(r.next())
            {
		if(r.getString(1).equals(passold)){
                    
                    r = st.executeQuery("update intervenant set passwd='"
                            +pass+"' where idi ="+interv);
                    

                }else{
                    
                    ret+= "Votre ancien mot de passe est incorrect !";
                }
                        		
            }else{
                
                ret+= "Erreur de connexion veilez réessayer !";
            }
		
            
           
            
		if (r != null) {
			r.close();
		}

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}

        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret += ex.getMessage();
        }

           
            return ret;
    }

    static String reinitialisePasswd(int interv, String pass) {
         String ret= "";
           
            
        try {
            
            Connection c = getConnection();
            Statement st = c.createStatement();
            
            int r = st.executeUpdate("update intervenant set passwd='"+pass+"' "
                     + " where idi="+interv);     
           if(r != 1){
               ret += "Problème de connexion veillez réessayer !";
           }
            
		

		if (st != null) {
			st.close();
		}

		if (c != null) {
			c.close();
		}

        } catch (SQLException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret += ex.getMessage();
        } catch (InstantiationException ex) {
            Logger.getLogger(BDUser.class.getName()).log(Level.SEVERE, null, ex);
            ret += ex.getMessage();
        }

           
            return ret;
    }

    
}
